package com.itxiaowu.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 *  报表工具类
 */
public class POIUtils {

    private final static String xls = "xls";
    private final static String xlsx = "xlsx";
    private final static String DATE_FORMAT = "yyyy/MM/dd";

    /**
     * 读取excel文件，解析后返回
     */
    public static List<String[]> readExcel(MultipartFile file) throws IOException {

        //检查文件
        checkFile(file);

        //获得workbook工作簿对象
        Workbook workbook = getWorkBook(file);

        //创建返回对象,把每行中的值作为一个数组,所有行作为一个集合返回
        List<String[]> list = new ArrayList<>();
        if(workbook != null){

            for(int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum ++){

                //获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);

                if(sheet == null){
                    continue;
                }
                //获得当前sheet的开始行
                int firstRowNum = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                //循环除了第一行的所有行
                for(int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++){

                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if(row == null){
                        continue;
                    }
                    //获得当前行的开始列
                    int firstCellNum = row.getFirstCellNum();

                    //获得当前行的列数
                    int lastCellNum = row.getPhysicalNumberOfCells();
                    String[] cells = new String[row.getPhysicalNumberOfCells()];

                    //循环当前行
                    for(int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++){
                        Cell cell = row.getCell(cellNum);
                        cells[cellNum] = getCellValue(cell);
                    }

                    list.add(cells);
                }

            }
            workbook.close();

        }
        return list;


    }

    /**
     * 获得单元格数据内容
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {

        String cellValue = "";
        if(cell == null){
            return cellValue;
        }

        //如果当前单元格内容为日期类型,需要进行特殊处理
        String dataFormatString = cell.getCellStyle().getDataFormatString();
        if(dataFormatString.equals("m/d/yy")){
            cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue());
            return cellValue;
        }

        //把数字当成String 来读,避免出现1读成1.0的情况
        if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
        // 判断数据的类型
        switch(cell.getCellType()){
            case Cell.CELL_TYPE_NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:   // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:   //boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK:  //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR:  //故障
                cellValue = "非法字符串";
                break;
        }
        return cellValue;

    }

    /**
     * 获得工作簿对象
     * @param file
     * @return
     */
    private static Workbook getWorkBook(MultipartFile file) {

        //获得文件名
        String fileName = file.getOriginalFilename();

        //创建Workbook工作簿对象，表示整个excel
        Workbook workbook = null;

        //获得excel文件的io流
        try {
            InputStream is = file.getInputStream();
            //根据文件名后缀不同(xls和xlsx)获得不同的workbook实现类对象
            if(fileName.endsWith(xls)){
                //2003
                workbook = new HSSFWorkbook(is);
            }else if(fileName.endsWith(xlsx)){
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return workbook;
    }

    /**
     *  校验文件是否合法
     * @param file
     */
    private static void checkFile(MultipartFile file) throws IOException {

        //判断文件是否存在
        if(file == null){
            throw new FileNotFoundException("文件不存在");
        }
        // 获得文件名
        String fileName = file.getOriginalFilename();

        // 判断文件是否是excel文件
        if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){

            throw new IOException(fileName + "不是excel文件");

        }

    }

}
